
Public Class DatasetHelper
    ' Nifty functions for data sets
    ' DataSetFromArrays
    ' DataTableFromArray (does not support indexed properties or nested collections)


    ' functions to convert arrays into DataSet and DataTable objects
    ' DataSetFromArrays & DataTableFromArray 
#Region "Convert arrays to DataSet and DataTable objects"

    ' wrapper for the DatasetHelper.DataTableFromArray that converts any number of arrays into a System.Data.DataSet
    Public Shared Function DataSetFromArrays(ByVal strName As String, ByVal ParamArray collections() As Array) As System.Data.DataSet
        Dim datasetOutput As System.Data.DataSet
        Dim tbl As System.Data.DataTable

        datasetOutput = New System.Data.DataSet(strName)
        For Each c As Array In collections
            If (c IsNot Nothing) Then
                tbl = DataTableFromArray(c)
                datasetOutput.Tables.Add(tbl)
            End If
        Next

        Return datasetOutput
    End Function

    ' creates a data table from any array, which can be real handy!
    Public Shared Function DataTableFromArray(ByRef collection As Array) As System.Data.DataTable
        ' create this table
        Dim tbl As System.Data.DataTable = New System.Data.DataTable()

        Dim typeSource As Type
        Dim arrayProperties As System.Reflection.PropertyInfo()
        Dim column As System.Data.DataColumn
        Dim rowTarget As System.Data.DataRow

        For Each rowSource As Object In collection
            typeSource = rowSource.GetType                      ' find out what type of object this one is

            arrayProperties = rowSource.GetType.GetProperties   ' get all the properties for this one

            rowTarget = tbl.NewRow()                            ' start a new line in the table
            For Each prop As System.Reflection.PropertyInfo In arrayProperties

                ' for now: skip indexed properties.  I don't need that yet and don't want to deal with it yet.  
                If (typeSource.GetProperty(prop.Name).GetIndexParameters().Length = 0) Then

                    ' make sure that the property exists as a column in the table
                    If (Not (tbl.Columns.Contains(prop.Name))) Then
                        ' if it's not there, add a column for this property
                        column = New System.Data.DataColumn(prop.Name, prop.PropertyType)
                        ' if this property can be read, but it can't be written, then it must be read-only!
                        column.ReadOnly = prop.CanRead And Not (prop.CanWrite)
                        ' add the property to the table
                        tbl.Columns.Add(column)

                        'Debug.Print("adding column: " & prop.Name)  ' not for normal use
                    End If

                    ' register this property in this row 
                    rowTarget(prop.Name) = typeSource.GetProperty(prop.Name).GetValue(rowSource, Nothing)

                Else
                    Debug.Print("indexed properties (" & prop.Name & ") not supported by Lib_VW::DataTableFromArray")
                    ' Fix Me: Add indexed properties feature to this handy library function!
                    ' the proper way to do this would be to add additional tables to a data set with an ID to act as a Primary Key (PK) connecting the indexed property to a row
                    ' but that is way beyond the scope of what I need right now, and I can't see a need for something that intricate for a good long time

                    Dim parametersIndex As System.Reflection.ParameterInfo()
                    parametersIndex = typeSource.GetProperty(prop.Name).GetIndexParameters()
                    For Each p As System.Reflection.ParameterInfo In parametersIndex
                        Debug.Print(p.Name & " : " & p.ParameterType.ToString & " : " & p.Position & " : " & p.DefaultValue.ToString)
                    Next
                End If
            Next

            ' k, now add the row for this item in the array
            tbl.Rows.Add(rowTarget)

        Next

        'Debug.Print(tbl.Columns.Count & " x " & tbl.Rows.Count) ' not for normal use

        ' return the table that we've created
        Return (tbl)
    End Function

#End Region


    ' grabbers that looks at a data row and safely grab a typed field by name 
    ' NumericFieldInDataRow, BooleanFieldInDataRow, DateFieldInDataRow & TextFieldInDataRow
#Region "FieldInDataRow grabbers"

    ' used to safely grab a numerical value from a data row.  the main purpose of this code is to compact the caller's code.  retrieving fields is usually done several at a time, and a function that does this over and over again would get big, fat and ugly
    Public Shared Function NumericFieldInDataRow(ByVal row As System.Data.DataRow, ByVal strField As String, ByVal doubleDefault As Double) As Double
        Dim value As Double

        ' check that the row is good and the field is good
        If ((row IsNot Nothing) AndAlso (row.Table IsNot Nothing) AndAlso (row.Table.Columns.Contains(strField)) AndAlso Not (row.IsNull(strField))) Then
            ' check to make sure that the data can be casted to a numeric
            If ((TypeOf row.Item(strField) Is Double) OrElse (TypeOf row.Item(strField) Is Single) OrElse (TypeOf row.Item(strField) Is Integer)) Then
                ' ok, we're good.  grab the data
                value = CDbl(row.Item(strField))
            Else
                ' data was the wrong type, assign a stupid default value
                value = doubleDefault
            End If
        Else
            ' row was bad, assign a stupid default value
            value = doubleDefault
        End If

        Return (value)
    End Function

    ' used to safely grab a boolean value from a data row.  the main purpose of this code is to compact the caller's code.  retrieving fields is usually done several at a time, and a function that does this over and over again would get big, fat and ugly
    Public Shared Function BooleanFieldInDataRow(ByVal row As System.Data.DataRow, ByVal strField As String, ByVal boolDefault As Boolean) As Boolean
        Dim value As Boolean

        ' check that the row is good and the field is good
        If ((row IsNot Nothing) AndAlso (row.Table IsNot Nothing) AndAlso (row.Table.Columns.Contains(strField)) AndAlso Not (row.IsNull(strField))) Then
            ' check to make sure that the data can be casted to a numeric
            If ((TypeOf row.Item(strField) Is Double) OrElse (TypeOf row.Item(strField) Is Single) OrElse (TypeOf row.Item(strField) Is Integer)) Then
                ' ok, we're good.  grab the data
                value = CBool(row.Item(strField))
            Else
                ' data was the wrong type, assign a stupid default value
                value = boolDefault
            End If
        Else
            ' row was bad, assign a stupid default value
            value = boolDefault
        End If

        Return (value)
    End Function

    ' used to safely grab a date value from a data row.  the main purpose of this code is to compact the caller's code.  retrieving fields is usually done several at a time, and a function that does this over and over again would get big, fat and ugly
    Public Shared Function DateFieldInDataRow(ByVal row As System.Data.DataRow, ByVal strField As String, ByVal dtDefault As DateTime) As DateTime
        Dim value As DateTime

        ' check that the row is good and the field is good
        If ((row IsNot Nothing) AndAlso (row.Table IsNot Nothing) AndAlso (row.Table.Columns.Contains(strField)) AndAlso Not (row.IsNull(strField))) Then
            ' check to make sure that the data can be casted to a numeric
            If (TypeOf row.Item(strField) Is DateTime) Then
                ' ok, we're good.  grab the data
                value = CDate(row.Item(strField))
            Else
                ' data was the wrong type, assign a stupid default value
                value = dtDefault
            End If
        Else
            ' row was bad, assign a stupid default value
            value = dtDefault
        End If

        Return (value)
    End Function

    ' used to safely grab a text string from a data row.  the main purpose of this code is to compact the caller's code.  retrieving fields is usually done several at a time, and a function that does this over and over again would get big, fat and ugly
    Public Shared Function TextFieldInDataRow(ByVal row As System.Data.DataRow, ByVal strField As String, ByVal strDefault As String) As String
        Dim value As String

        ' check that the row is good and the field is good
        If ((row IsNot Nothing) AndAlso (row.Table IsNot Nothing) AndAlso (row.Table.Columns.Contains(strField)) AndAlso Not (row.IsNull(strField))) Then
            ' pretty much any value can be converted to a string
            value = row.Item(strField).ToString()
        Else
            ' row was bad, assign a stupid default value
            value = strDefault
        End If

        Return (value)
    End Function

#End Region


    ' Export functions: 
    ' ExportXLS & ExportXML
#Region "Export functions"


    ' This converts an array to a data table, using the DatasetHelper.DataTableFromArray function, and then writes that data table to an XML file, using the other DatasetHelper.ExportXML function
    Public Shared Sub ExportXML(ByRef arrayOutput As Array, ByRef strDataSet As String, ByRef strTable As String, ByRef strFullPathName As String)
        Dim setOutput As System.Data.DataSet                ' this is a data set; contains the table we want to output
        Dim tblOutput As System.Data.DataTable              ' this is a table from the data binding

        ' create a data set, with a table, with the data in the bindButterflies collection
        setOutput = New System.Data.DataSet(strDataSet)     ' create the output dataset (initially empty)
        tblOutput = DataTableFromArray(arrayOutput)         ' convert the array to a table
        tblOutput.TableName = strTable                      ' give it a name, for future reference.  There's only one table in this data set (so far), but data sets require each table to have a unique name
        setOutput.Tables.Add(tblOutput)                     ' add the table to the output dataset

        ' write the data set to an XML file, so that SRSS can use it as a data source
        ExportXML(setOutput, strFullPathName)
    End Sub

    ' This writes a dataset to an xml file.  Nifty!
    Public Shared Sub ExportXML(ByRef setOutput As System.Data.DataSet, ByRef strFullPathName As String)
        Dim writerOutput As System.Xml.XmlWriter            ' this is the file stream we'll write out data to

        ' write the data set to an XML file, so that SRSS can use it as a data source
        writerOutput = New System.Xml.XmlTextWriter(strFullPathName, System.Text.Encoding.ASCII)      ' ASCII is completely arbitrary
        setOutput.WriteXml(writerOutput, System.Data.XmlWriteMode.IgnoreSchema)       ' IgnoreSchema is completely arbitrary
        writerOutput.Close()                                ' close the file to let SRSS take it over
    End Sub


    ' writes a dataset as an XLS to a System.Web.HttpResponse output stream

    Public Shared Sub ExportXLS(ByVal ds As System.Data.DataSet, ByVal DefaultFileName As String, ByVal response As System.Web.HttpResponse)
        'This function writes all of the tables in the dataset to the response object.
        'first let's clean up the response.object
        response.Clear()
        response.Charset = ""
        'set the response mime type for excel
        response.ContentType = "application/vnd.ms-excel"
        'set the default file name
        response.AddHeader("Content-Disposition", "attachment;filename=""" & DefaultFileName & """")
        'create a string writer
        Dim stringWrite As New System.IO.StringWriter
        'create an htmltextwriter which uses the stringwriter
        Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
        'instantiate a datagrid
        Dim dg As New System.Web.UI.WebControls.DataGrid
        'set the datagrid datasource to the dataset passed in
        Dim i As Integer
        For i = 0 To ds.Tables.Count() - 1
            If ds.Tables(i).Rows.Count > 0 Then
                'StartWorksheet(htmlWrite, i + 1)
                dg.DataSource = ds.Tables(i)
                'bind the datagrid
                dg.DataBind()
                'tell the datagrid to render itself to our htmltextwriter
                dg.RenderControl(htmlWrite)
                'EndWorksheet(htmlWrite)
            End If
        Next
        'all that's left is to output the html

        response.Write(stringWrite.ToString)
        'System.Web.HttpContext.Current.ApplicationInstance.CompleteRequest()
        'response.End()

    End Sub

    Public Shared Sub ExportXLS(ByVal ds As System.Data.DataSet, ByVal DefaultFileName As String, ByVal TableIndex As Integer, ByVal response As System.Web.HttpResponse)
        'lets make sure a table actually exists at the passed in value
        'if it is not call the base method
        If TableIndex > ds.Tables.Count - 1 Then
            ExportXLS(ds, DefaultFileName, response)
        End If
        'we've got a good table so
        'let's clean up the response.object
        response.Clear()
        response.Charset = ""
        'set the response mime type for excel
        response.ContentType = "application/vnd.ms-excel"
        'set the default file name
        response.AddHeader("Content-Disposition", "attachment;filename=""" & DefaultFileName & """")
        'create a string writer
        Dim stringWrite As New System.IO.StringWriter
        'create an htmltextwriter which uses the stringwriter
        Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
        'instantiate a datagrid
        Dim dg As New System.Web.UI.WebControls.DataGrid
        'set the datagrid datasource to the dataset passed in
        dg.DataSource = ds.Tables(TableIndex)
        'bind the datagrid
        dg.DataBind()
        'tell the datagrid to render itself to our htmltextwriter
        dg.RenderControl(htmlWrite)
        'all that's left is to output the html
        response.Write(stringWrite.ToString)
        response.End()
    End Sub

    Public Shared Sub ExportXLS(ByVal ds As System.Data.DataSet, ByVal DefaultFileName As String, ByVal TableName As String, ByVal response As System.Web.HttpResponse)
        'let's make sure the table name exists
        'if it does not then call the default method
        If ds.Tables(TableName) Is Nothing Then
            ExportXLS(ds, DefaultFileName, response)
        End If
        'we've got a good table so
        'let's clean up the response.object
        response.Clear()
        response.Charset = ""
        'set the response mime type for excel
        response.ContentType = "application/vnd.ms-excel"
        'set the default file name
        response.AddHeader("Content-Disposition", "attachment;filename=""" & DefaultFileName & """")
        'create a string writer
        Dim stringWrite As New System.IO.StringWriter
        'create an htmltextwriter which uses the stringwriter
        Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
        'instantiate a datagrid
        Dim dg As New System.Web.UI.WebControls.DataGrid
        'set the datagrid datasource to the dataset passed in
        dg.DataSource = ds.Tables(TableName)
        'bind the datagrid
        dg.DataBind()
        'tell the datagrid to render itself to our htmltextwriter
        dg.RenderControl(htmlWrite)
        'all that's left is to output the html
        response.Write(stringWrite.ToString)
        response.End()
    End Sub


    ' output as an XLS to a System.IO.TextWriter output stream

    Public Shared Sub ExportXLS(ByVal ds As System.Data.DataSet, ByVal DefaultFileName As String, ByVal response As System.IO.TextWriter)
        'This function writes all of the tables in the dataset to the response object.
        'first let's clean up the response.object

        'create a string writer
        Dim stringWrite As New System.IO.StringWriter
        'create an htmltextwriter which uses the stringwriter
        Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
        'instantiate a datagrid
        Dim dg As New System.Web.UI.WebControls.DataGrid
        'set the datagrid datasource to the dataset passed in
        Dim i As Integer
        For i = 0 To ds.Tables.Count() - 1
            If ds.Tables(i).Rows.Count > 0 Then
                'StartWorksheet(htmlWrite, i + 1)
                dg.DataSource = ds.Tables(i)
                'bind the datagrid
                dg.DataBind()
                'tell the datagrid to render itself to our htmltextwriter
                dg.RenderControl(htmlWrite)
                'EndWorksheet(htmlWrite)
            End If
        Next
        'all that's left is to output the html

        response.Write(stringWrite.ToString)
        'System.Web.HttpContext.Current.ApplicationInstance.CompleteRequest()
        response.Flush()

    End Sub

    Public Shared Sub ExportXLS(ByVal ds As System.Data.DataSet, ByVal DefaultFileName As String, ByVal TableIndex As Integer, ByVal response As System.IO.TextWriter)
        'lets make sure a table actually exists at the passed in value
        'if it is not call the base method
        If TableIndex > ds.Tables.Count - 1 Then
            ExportXLS(ds, DefaultFileName, response)
        End If

        'create a string writer
        Dim stringWrite As New System.IO.StringWriter
        'create an htmltextwriter which uses the stringwriter
        Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
        'instantiate a datagrid
        Dim dg As New System.Web.UI.WebControls.DataGrid
        'set the datagrid datasource to the dataset passed in
        dg.DataSource = ds.Tables(TableIndex)
        'bind the datagrid
        dg.DataBind()
        'tell the datagrid to render itself to our htmltextwriter
        dg.RenderControl(htmlWrite)
        'all that's left is to output the html
        response.Write(stringWrite.ToString)
        response.Flush()

    End Sub

    Public Shared Sub ExportXLS(ByVal ds As System.Data.DataSet, ByVal DefaultFileName As String, ByVal TableName As String, ByVal response As System.IO.TextWriter)
        'let's make sure the table name exists
        'if it does not then call the default method
        If ds.Tables(TableName) Is Nothing Then
            ExportXLS(ds, DefaultFileName, response)
        End If

        'create a string writer
        Dim stringWrite As New System.IO.StringWriter
        'create an htmltextwriter which uses the stringwriter
        Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
        'instantiate a datagrid
        Dim dg As New System.Web.UI.WebControls.DataGrid
        'set the datagrid datasource to the dataset passed in
        dg.DataSource = ds.Tables(TableName)
        'bind the datagrid
        dg.DataBind()
        'tell the datagrid to render itself to our htmltextwriter
        dg.RenderControl(htmlWrite)
        'all that's left is to output the html
        response.Write(stringWrite.ToString)
        response.Flush()
    End Sub


    Private Shared Sub StartWorksheet(ByVal writer As System.IO.TextWriter, ByVal iSheetNum As Integer)
        writer.Write("<Worksheet name=""Sheet" & iSheetNum.ToString() & """>")
    End Sub
    Private Shared Sub EndWorksheet(ByVal writer As System.IO.TextWriter)
        writer.Write("</Worksheet>")
    End Sub


#End Region

End Class

